<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>JDBC编程 | 浪飞IT小栈</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/img/favicon.ico">
    <meta name="description" content="前后端技术分享">
    <meta name="keywords" content="前端博客,后端博客,人工智能,个人技术博客,前端,前端开发,前端框架,web前端,前端面试题,技术文档,学习,面试,JavaScript,js,ES6,TypeScript,vue,python,css3,html5,Node,git,github,markdown,java,Spring,Springboot,Redis,mysql">
    <meta name="baidu-site-verification" content="7F55weZDDc">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/assets/css/0.styles.ada71c49.css" as="style"><link rel="preload" href="/assets/js/app.a2a00aa5.js" as="script"><link rel="preload" href="/assets/js/2.256f807a.js" as="script"><link rel="preload" href="/assets/js/34.45e8e898.js" as="script"><link rel="prefetch" href="/assets/js/10.ab144fe3.js"><link rel="prefetch" href="/assets/js/11.062e0879.js"><link rel="prefetch" href="/assets/js/12.f9c60882.js"><link rel="prefetch" href="/assets/js/13.0a6ebfcf.js"><link rel="prefetch" href="/assets/js/14.03fc6f12.js"><link rel="prefetch" href="/assets/js/15.19dbf9fd.js"><link rel="prefetch" href="/assets/js/16.6e68160b.js"><link rel="prefetch" href="/assets/js/17.1d678834.js"><link rel="prefetch" href="/assets/js/18.4536fd5c.js"><link rel="prefetch" href="/assets/js/19.9c1b65df.js"><link rel="prefetch" href="/assets/js/20.c5f96e71.js"><link rel="prefetch" href="/assets/js/21.3f0b334e.js"><link rel="prefetch" href="/assets/js/22.55c868be.js"><link rel="prefetch" href="/assets/js/23.91286241.js"><link rel="prefetch" href="/assets/js/24.c1af1909.js"><link rel="prefetch" href="/assets/js/25.a995a755.js"><link rel="prefetch" href="/assets/js/26.abc5dea3.js"><link rel="prefetch" href="/assets/js/27.1eb62850.js"><link rel="prefetch" href="/assets/js/28.b383a871.js"><link rel="prefetch" href="/assets/js/29.088e0d73.js"><link rel="prefetch" href="/assets/js/3.cd716564.js"><link rel="prefetch" href="/assets/js/30.3ccc98d5.js"><link rel="prefetch" href="/assets/js/31.aab7cc23.js"><link rel="prefetch" href="/assets/js/32.5e16b7e6.js"><link rel="prefetch" href="/assets/js/33.4aaeb741.js"><link rel="prefetch" href="/assets/js/35.5899c97a.js"><link rel="prefetch" href="/assets/js/36.32e2e263.js"><link rel="prefetch" href="/assets/js/37.c4e27bbd.js"><link rel="prefetch" href="/assets/js/38.fe5e60d2.js"><link rel="prefetch" href="/assets/js/39.2cb0b4ee.js"><link rel="prefetch" href="/assets/js/4.edf4d5e7.js"><link rel="prefetch" href="/assets/js/40.08d5bead.js"><link rel="prefetch" href="/assets/js/41.2f80f24b.js"><link rel="prefetch" href="/assets/js/42.43a49913.js"><link rel="prefetch" href="/assets/js/43.08da7ec2.js"><link rel="prefetch" href="/assets/js/44.7c7cb8a9.js"><link rel="prefetch" href="/assets/js/45.0ddf7e7f.js"><link rel="prefetch" href="/assets/js/46.7c4609a5.js"><link rel="prefetch" href="/assets/js/47.edf3ddf8.js"><link rel="prefetch" href="/assets/js/48.569a90b9.js"><link rel="prefetch" href="/assets/js/49.fa368ac8.js"><link rel="prefetch" href="/assets/js/5.24054156.js"><link rel="prefetch" href="/assets/js/50.b9b3ea7f.js"><link rel="prefetch" href="/assets/js/51.a2ebf0fe.js"><link rel="prefetch" href="/assets/js/52.31f02238.js"><link rel="prefetch" href="/assets/js/53.4110ef1e.js"><link rel="prefetch" href="/assets/js/54.dab741c7.js"><link rel="prefetch" href="/assets/js/55.f27984e9.js"><link rel="prefetch" href="/assets/js/56.bd4c1a8e.js"><link rel="prefetch" href="/assets/js/57.375d40cb.js"><link rel="prefetch" href="/assets/js/58.932e3864.js"><link rel="prefetch" href="/assets/js/59.e657be1b.js"><link rel="prefetch" href="/assets/js/6.b259c061.js"><link rel="prefetch" href="/assets/js/60.88d94274.js"><link rel="prefetch" href="/assets/js/61.fa71d884.js"><link rel="prefetch" href="/assets/js/62.a9d3b3b7.js"><link rel="prefetch" href="/assets/js/63.f849fe75.js"><link rel="prefetch" href="/assets/js/64.1770f7a9.js"><link rel="prefetch" href="/assets/js/65.6afc33db.js"><link rel="prefetch" href="/assets/js/66.799f2eb4.js"><link rel="prefetch" href="/assets/js/67.f36b2f7d.js"><link rel="prefetch" href="/assets/js/68.868cd00b.js"><link rel="prefetch" href="/assets/js/69.4c29b907.js"><link rel="prefetch" href="/assets/js/7.e34ea00f.js"><link rel="prefetch" href="/assets/js/70.1b993ed4.js"><link rel="prefetch" href="/assets/js/71.e16ad4f9.js"><link rel="prefetch" href="/assets/js/72.627505e6.js"><link rel="prefetch" href="/assets/js/73.32768e14.js"><link rel="prefetch" href="/assets/js/74.968a580d.js"><link rel="prefetch" href="/assets/js/75.42d5af3d.js"><link rel="prefetch" href="/assets/js/76.19a69d31.js"><link rel="prefetch" href="/assets/js/77.cf627a78.js"><link rel="prefetch" href="/assets/js/78.07177880.js"><link rel="prefetch" href="/assets/js/8.c4dd00ce.js"><link rel="prefetch" href="/assets/js/9.0ff73be2.js">
    <link rel="stylesheet" href="/assets/css/0.styles.ada71c49.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/img/logo.png" alt="浪飞IT小栈" class="logo"> <span class="site-name can-hide">浪飞IT小栈</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><a href="/pages/6138ae/" class="nav-link">Java速通</a></div><div class="nav-item"><a href="/pages/705b35/" class="nav-link">面试经典</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="精品项目" class="dropdown-title"><a href="/project/" class="link-title">精品项目</a> <span class="title" style="display:none;">精品项目</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>桌面端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/pages/8143cc480faf9a11/" class="nav-link">JavaScript</a></li></ul></li><li class="dropdown-item"><h4>PC后端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/javascript/" class="nav-link">项目1</a></li><li class="dropdown-subitem"><a href="/note/typescript-axios/" class="nav-link">项目二</a></li></ul></li></ul></div></div><div class="nav-item"><a href="/about/" class="nav-link">电子书</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div><div class="nav-item"><a href="/pages/0c07b2/" class="nav-link">IT杂货铺</a></div><div class="nav-item"><a href="/archives/" class="nav-link">更多资源</a></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><div class="blogger"><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/avatar.jpg"> <div class="blogger-info"><h3>浪飞yes</h3> <span>无法简单的人儿~</span></div></div> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><a href="/pages/6138ae/" class="nav-link">Java速通</a></div><div class="nav-item"><a href="/pages/705b35/" class="nav-link">面试经典</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="精品项目" class="dropdown-title"><a href="/project/" class="link-title">精品项目</a> <span class="title" style="display:none;">精品项目</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>桌面端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/pages/8143cc480faf9a11/" class="nav-link">JavaScript</a></li></ul></li><li class="dropdown-item"><h4>PC后端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/javascript/" class="nav-link">项目1</a></li><li class="dropdown-subitem"><a href="/note/typescript-axios/" class="nav-link">项目二</a></li></ul></li></ul></div></div><div class="nav-item"><a href="/about/" class="nav-link">电子书</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div><div class="nav-item"><a href="/pages/0c07b2/" class="nav-link">IT杂货铺</a></div><div class="nav-item"><a href="/archives/" class="nav-link">更多资源</a></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>开篇</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>基础语法</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>面向对象</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>常用类</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>基础高级</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>数据库</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/826257/" class="sidebar-link">MySQL基础</a></li><li><a href="/pages/5ae2e1/" aria-current="page" class="active sidebar-link">JDBC编程</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#一、今日学习内容与目标" class="sidebar-link">一、今日学习内容与目标</a></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#二、jdbc-概述" class="sidebar-link">二、JDBC 概述</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_2-1-持久化概述" class="sidebar-link">2.1 持久化概述</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_2-2-jdbc-概述" class="sidebar-link">2.2 JDBC 概述</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#三、jdbc-快速入门" class="sidebar-link">三、JDBC 快速入门</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_3-1-环境准备" class="sidebar-link">3.1 环境准备</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_3-2-jdbc-开发口诀" class="sidebar-link">3.2 JDBC 开发口诀</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_3-3-jdbc-快速入门" class="sidebar-link">3.3 JDBC 快速入门</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_3-3-1-创建数据库和表" class="sidebar-link">3.3.1 创建数据库和表</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_3-3-2-完成插入功能" class="sidebar-link">3.3.2 完成插入功能</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#四、功能类详解" class="sidebar-link">四、功能类详解</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_4-1-drivermanager-类" class="sidebar-link">4.1 DriverManager 类</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_4-2-connection-类" class="sidebar-link">4.2 Connection 类</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_4-3-statement-类" class="sidebar-link">4.3 Statement 类</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#五、dao-思想" class="sidebar-link">五、DAO 思想</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_5-1-dao-引入" class="sidebar-link">5.1 DAO 引入</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_5-2-dao-介绍和方法设计" class="sidebar-link">5.2 DAO 介绍和方法设计</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_5-3-dao层开发规范-掌握-背" class="sidebar-link">5.3 DAO层开发规范(掌握,背)</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_5-4-根据-dao-规范搭建项目" class="sidebar-link">5.4 根据 DAO 规范搭建项目</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_5-4-1-步骤" class="sidebar-link">5.4.1 步骤</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_5-4-2-测试先行" class="sidebar-link">5.4.2 测试先行</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_5-4-3-dao-之保存操作" class="sidebar-link">5.4.3 DAO 之保存操作</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#六、预编译语句对象" class="sidebar-link">六、预编译语句对象</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_6-1-预编译语句对象简介" class="sidebar-link">6.1 预编译语句对象简介</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_6-2-api-详解-掌握" class="sidebar-link">6.2 API 详解（掌握）</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_6-3-插入功能优化" class="sidebar-link">6.3 插入功能优化</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#七、基于-dao-案例" class="sidebar-link">七、基于 DAO 案例</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_7-1-dao-之更新、删除" class="sidebar-link">7.1 DAO 之更新、删除</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_7-2-dao-之查询操作" class="sidebar-link">7.2 DAO 之查询操作</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_7-2-1-查询结果对象" class="sidebar-link">7.2.1_查询结果对象</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_7-2-2-查询实现" class="sidebar-link">7.2.2 查询实现</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#八、重构设计" class="sidebar-link">八、重构设计</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_8-1-抽取-jdbcutil-工具类" class="sidebar-link">8.1 抽取 JDBCUtil 工具类</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_8-2-抽取db-properties" class="sidebar-link">8.2 抽取db.properties</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#九、jdbc-事务操作" class="sidebar-link">九、JDBC 事务操作</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_9-1-银行转账案例" class="sidebar-link">9.1 银行转账案例</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_9-1-1-准备-account-账户" class="sidebar-link">9.1.1 准备 account (账户)</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_9-1-2-转账操作步骤" class="sidebar-link">9.1.2 转账操作步骤</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_9-2-jdbc-的事务操作" class="sidebar-link">9.2 JDBC 的事务操作</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_9-2-1-事务的-acid-属性-作用" class="sidebar-link">9.2.1 事务的 ACID 属性(作用)</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_9-2-2-事务的操作步骤" class="sidebar-link">9.2.2 事务的操作步骤</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_9-2-3-事务的操作模板-掌握" class="sidebar-link">9.2.3 事务的操作模板(掌握)</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_9-2-4-转账案例优化" class="sidebar-link">9.2.4 转账案例优化</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_9-2-5-事务相关注意事项" class="sidebar-link">9.2.5 事务相关注意事项</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#十、连接池思想" class="sidebar-link">十、连接池思想</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_10-1-连接池引入和介绍" class="sidebar-link">10.1 连接池引入和介绍</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_10-2-使没使用连接池的区别" class="sidebar-link">10.2 使没使用连接池的区别</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_10-3-常见的-datasource-实现" class="sidebar-link">10.3 常见的 DataSource 实现</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_10-4-druid-连接池使用" class="sidebar-link">10.4 druid 连接池使用</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_10-5-连接池其他功能" class="sidebar-link">10.5 连接池其他功能</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#十一、sql-注入详解" class="sidebar-link">十一、SQL 注入详解</a></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#十二、jdbc操作重构" class="sidebar-link">十二、JDBC操作重构</a></li><li class="sidebar-sub-header level2"><a href="/pages/5ae2e1/#十三、小结与作业" class="sidebar-link">十三、小结与作业</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_13-1-小结" class="sidebar-link">13.1 小结</a></li><li class="sidebar-sub-header level3"><a href="/pages/5ae2e1/#_13-2-作业" class="sidebar-link">13.2 作业</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_13-2-1-题目1" class="sidebar-link">13.2.1 题目1</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_13-2-2-题目2" class="sidebar-link">13.2.2 题目2</a></li><li class="sidebar-sub-header level4"><a href="/pages/5ae2e1/#_13-2-3-题目3" class="sidebar-link">13.2.3 题目3</a></li></ul></li></ul></li><li><a href="/pages/9980e4/" class="sidebar-link">MyBatis框架初级</a></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>前端技术</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>热门框架</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>前后端分离</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>权限控制</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>NoSQL</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>微服务</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>消息中间件</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>脚手架</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>技术增值</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/categories/?category=Java%E9%80%9F%E9%80%9A" title="分类" data-v-06225672>Java速通</a></li><li data-v-06225672><a href="/categories/?category=%E6%95%B0%E6%8D%AE%E5%BA%93" title="分类" data-v-06225672>数据库</a></li></ul> <div class="info" data-v-06225672><div title="作者" class="author iconfont icon-touxiang" data-v-06225672><a href="https://github.com/langfeiyes" target="_blank" title="作者" class="beLink" data-v-06225672>langfeiyes</a></div> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2024-03-03</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">JDBC编程<!----></h1>  <div class="theme-vdoing-content content__default"><h1 id="jdbc编程"><a href="#jdbc编程" class="header-anchor">#</a> JDBC编程</h1> <h2 id="一、今日学习内容与目标"><a href="#一、今日学习内容与目标" class="header-anchor">#</a> 一、今日学习内容与目标</h2> <ul><li>理解什么是持久化操作</li> <li>理解 JDBC 的作用</li> <li>掌握使用 JDBC 完成 CRUD 操作</li> <li>理解 DAO 思想</li> <li>掌握 DAO 规范 (包的分类,和类的起名)</li> <li>理解并掌握基于 JDBC 的 DAO 案例</li> <li>理解 ResultSet 结果集对象的作用和应用</li> <li>掌握事务的作用</li> <li>掌握银行转账无事务存在的问题</li> <li>掌握事务的使用</li> <li>掌握连接池思想</li> <li>掌握 druid 连接池的使用</li> <li>理解 SQL 注入的逻辑</li> <li>理解JDBC操作重构</li></ul> <h2 id="二、jdbc-概述"><a href="#二、jdbc-概述" class="header-anchor">#</a> 二、JDBC 概述</h2> <h3 id="_2-1-持久化概述"><a href="#_2-1-持久化概述" class="header-anchor">#</a> 2.1 持久化概述</h3> <p><strong>持久化(persistence)</strong>:   把内存中的数据保存到可掉电式存储设备 (硬盘、U盘等) 中以供之后使用。</p> <p>大多数企业级应用，数据持久化是将数据保存到各种关系数据库， 而在 Java中，数据库中数据的存取技术只能通过 JDBC 来访问数据库。  JDBC 是 Java 语言访问数据库的基石,其他技术都是对 JDBC 的封装.</p> <h3 id="_2-2-jdbc-概述"><a href="#_2-2-jdbc-概述" class="header-anchor">#</a> 2.2 JDBC 概述</h3> <p><strong>JDBC 介绍</strong></p> <ul><li>JDBC（Java DataBase Connectivity  <span style="color:red;">Java 数据库连接</span>）是一种用于执行 SQL 语句的 Java API，<strong>可以为多种关系型数据库提供统一访问</strong>，它由一组用 Java 语言编写的类和接口组成。</li></ul> <p><strong>JDBC 本质</strong></p> <ul><li>其实就是Java 官方提供的一套规范(接口)，帮助开发人员快速实现不同关系型数据库的连接。</li></ul> <p><em><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/JDBCinfo.jpg" alt=""></em></p> <p><strong>总结</strong> : JDBC 本身是 Java 连接数据库的标准,由 Java 编写的一组接口组成，接口的实现由各个数据库厂商来完成.</p> <h2 id="三、jdbc-快速入门"><a href="#三、jdbc-快速入门" class="header-anchor">#</a> 三、JDBC 快速入门</h2> <h3 id="_3-1-环境准备"><a href="#_3-1-环境准备" class="header-anchor">#</a> 3.1 环境准备</h3> <ol><li><p>拷贝 MySQL 的 JDBC 驱动,到 Java 项目中: mysql-connector-java-5.1.26-bin.jar</p> <p>注意: 是 jar 包,不是 zip 包.</p></li> <li><p>选择 jar,把 jar 引用到 classpath 路径.</p> <ul><li><p>idea 项目中创建一个目录 lib</p></li> <li><p>右键 lib 目录，点 Add as Library...</p> <p><em><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20211108190030242.png" alt="image-20211108190030242"></em></p></li></ul></li></ol> <h3 id="_3-2-jdbc-开发口诀"><a href="#_3-2-jdbc-开发口诀" class="header-anchor">#</a> 3.2 JDBC 开发口诀</h3> <p><strong>贾琏欲执事</strong></p> <ol><li><strong>加</strong>载注册驱动.</li> <li>获取<strong>连</strong>接对象.</li> <li>创建/获取<strong>语</strong>句执行对象.</li> <li><strong>执</strong>行SQL语句.</li> <li><strong>释</strong>放资源.</li></ol> <h3 id="_3-3-jdbc-快速入门"><a href="#_3-3-jdbc-快速入门" class="header-anchor">#</a> 3.3 JDBC 快速入门</h3> <p>需求: 使用 JDBC 的 API 完成向学生表中插入一条数据.</p> <h4 id="_3-3-1-创建数据库和表"><a href="#_3-3-1-创建数据库和表" class="header-anchor">#</a> 3.3.1 创建数据库和表</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 创建 jdbcdemo 数据库</span>
<span class="token keyword">CREATE</span> <span class="token keyword">DATABASE</span> jdbcdemo<span class="token punctuation">;</span>

<span class="token comment">-- 创建 学生表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t_student<span class="token punctuation">(</span>
	id	  <span class="token keyword">BIGINT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>	<span class="token comment">-- 主键 自增</span>
  	name  <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>							<span class="token comment">-- 姓名</span>
    age   <span class="token keyword">INT</span><span class="token punctuation">,</span>									<span class="token comment">-- 年龄</span>
  	email <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>							<span class="token comment">-- 邮箱</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><h4 id="_3-3-2-完成插入功能"><a href="#_3-3-2-完成插入功能" class="header-anchor">#</a> 3.3.2 完成插入功能</h4> <p>在 cn.wolfcode.day07.hello.JDBCDemo.java 中完成插入功能</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">JDBCDemo</span> <span class="token punctuation">{</span>
    <span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">main</span><span class="token punctuation">(</span><span class="token class-name">String</span><span class="token punctuation">[</span><span class="token punctuation">]</span> args<span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">Exception</span> <span class="token punctuation">{</span>
        <span class="token comment">//1. 加载注册驱动</span>
        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">//2. 获取连接对象</span>
        <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">&quot;jdbc:mysql://127.0.0.1:3306/jdbcdemo&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;admin&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">//3. 创建/获取语句对象</span>
        <span class="token class-name">Statement</span> st <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">createStatement</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">//4. 执行SQL语句</span>
        <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;INSERT INTO t_student(name,age,email) VALUES('存哥',18,'willie@wolfcode.cn')&quot;</span><span class="token punctuation">;</span>
        st<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">//5. 释放资源</span>
        st<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><h2 id="四、功能类详解"><a href="#四、功能类详解" class="header-anchor">#</a> 四、功能类详解</h2> <h3 id="_4-1-drivermanager-类"><a href="#_4-1-drivermanager-类" class="header-anchor">#</a> 4.1 DriverManager 类</h3> <p>DriverManager ： 驱动管理对象，主要用于加载注册关系型数据库的 Driver 类以及获取和关系型数据库的连接对象</p> <ol><li><p><strong>加载注册驱动</strong></p> <ul><li><p>注册给定的驱动对象：static void registerDriver(Driver driver);</p></li> <li><p>咱们代码写法：Class.forName(&quot;com.mysql.jdbc.Driver&quot;);</p></li> <li><p>在 com.mysql.jdbc.Driver 类中存在静态代码块</p></li> <li><p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/jdbc2.png" alt="image-20200225005941024"></p></li> <li><blockquote><ol><li>把 com.mysql.jdbc.Driver 这一份字节码加载进 JVM.</li> <li>字节码被加载进JVM,就会执行其静态代码块.而其底层的静态代码块在完成注册驱动工作，将驱动注册到 DriverManger 中.</li></ol></blockquote></li> <li><p><strong>注意</strong>:  Java6 开始,JDBC4.0 有一个新特性-无需加载注册驱动.</p> <p>在 jar 包中存在一个java.sql.Driver 配置文件。此文件已经包含 com.mysql.jdbc.Driver 驱动名称。
程序会自动从 META-INF/services/java.sql.Driver 去读取当前的驱动类的全限定名,所以目前写不写加载注册驱动都没问题，但是 web 项目必须写上加载注册驱动代码，否则无法连接数据库。</p></li></ul></li> <li><p>**获取连接对象 **</p> <ul><li>获取连接对象：static Connection getConnection(String url,String username,String password);</li> <li>参数：
<ul><li>url ：指定连接某一个数据库的路径，语法：url=jdbc:mysql://localhost:3306/jdbcdemo
<ul><li>MySQL url: 如果连接的是本机的 MySQL,并且端口是默认 3306 ,则可以简写: url=jdbc:mysql:///jdbcdemo</li></ul></li> <li>username ：连接数据库的账号</li> <li>password ：连接数据库的密码</li></ul></li></ul></li></ol> <h3 id="_4-2-connection-类"><a href="#_4-2-connection-类" class="header-anchor">#</a> 4.2 Connection 类</h3> <p>Connection ：关系型数据库连接对象，相当于 Java 程序和数据库的通信桥梁</p> <ol><li><p>获取语句执行对象</p> <ul><li>获取静态语句执行对象: Statement createStatement();</li> <li>获取预编译语句执行对象: PreparedStatement prepareStatement(String sql);</li></ul></li> <li><p>释放资源</p> <ul><li>关闭连接对象: void close();</li></ul></li> <li><p>事务</p></li></ol> <h3 id="_4-3-statement-类"><a href="#_4-3-statement-类" class="header-anchor">#</a> 4.3 Statement 类</h3> <p>Statement：静态 SQL 语句执行对象，用于执行字符串的 SQL 语句</p> <ol><li><p>执行 DML / DDL 语句 : int executeUpdate(String sql);</p> <ul><li>返回值 ： DML 受影响的行数,DDL 则返回 0</li> <li>参数：insert、update、delete、create、drop 语句</li></ul></li> <li><p>执行 DQL 语句：ResultSet executeQuery(String sql);</p> <ul><li>返回值：执行查询之后的结果数据</li> <li>参数：select 语句</li></ul></li> <li><p>释放资源</p> <ul><li>关闭 Statement 对象 ：void close();</li></ul></li></ol> <h2 id="五、dao-思想"><a href="#五、dao-思想" class="header-anchor">#</a> 五、DAO 思想</h2> <h3 id="_5-1-dao-引入"><a href="#_5-1-dao-引入" class="header-anchor">#</a> 5.1 DAO 引入</h3> <p>在没有 DAO 的时候,我们的代码存在大量的重复。</p> <p><em><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200423003843520.png" alt="" width="450px"></em></p> <p>有了 DAO 之后，把 CRUD 的功能抽取成了工具类中的工具方法,需要时直接调用即可。</p> <h3 id="_5-2-dao-介绍和方法设计"><a href="#_5-2-dao-介绍和方法设计" class="header-anchor">#</a> 5.2 DAO 介绍和方法设计</h3> <p><strong>DAO</strong>(Data Access Object) <a href="https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E8%AE%BF%E9%97%AE%E5%AF%B9%E8%B1%A1/3351868" target="_blank" rel="noopener noreferrer">数据访问对象<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>是一个<a href="https://baike.baidu.com/item/%E9%9D%A2%E5%90%91%E5%AF%B9%E8%B1%A1/2262089" target="_blank" rel="noopener noreferrer">面向对象<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>的<a href="https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93/103728" target="_blank" rel="noopener noreferrer">数据库<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>接口. 顾名思义就是与数据库打交道，将所有对数据源的访问操作抽象封装在一个公共 API 中。</p> <p>DAO 中的主要操作: 增删改查(CRUD). 引入 DAO 之后,此时设计如下图。</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200423004237433.png" alt="image-20200423004237433"></p> <p>通过以上图，DAO 作为组件，主要的是方法的设计，而方法设计需要注意什么呢？</p> <ol><li><p>在保存功能中,调用者需要传递多个参数进来,然后把这些数据保存到数据库中</p></li> <li><p>在查询功能中,结果集的每行数据有多个列的值,然后把这些数据返回给调用者</p></li></ol> <p><strong>意识</strong>: 在开发过程中,如果遇到需要传递的数据有多个的时候,通常需要使用 JavaBean 对其进行封装</p> <p>最终方法设计如下：</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">//调用者将需要保存的数据封装到Student对象中,然后传递进来 </span>
<span class="token keyword">void</span> <span class="token function">insert</span><span class="token punctuation">(</span><span class="token class-name">Student</span> stu<span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token comment">//在查询之后,将每行数据封装到Student对象中,再返回给调用者 </span>
<span class="token class-name">Student</span> <span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token keyword">long</span> id<span class="token punctuation">)</span><span class="token punctuation">;</span> 
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="_5-3-dao层开发规范-掌握-背"><a href="#_5-3-dao层开发规范-掌握-背" class="header-anchor">#</a> 5.3 DAO层开发规范(掌握,背)</h3> <p>规范就是王八的屁股---&gt;龟腚(规定)</p> <p>DAO 其实是一个组件(可以重复使用),包括:</p> <p><strong>分包规范:</strong>
域名倒写.项目模块名.组件;
cn.wolfcode.pss.util;            // 存放工具类 StringUtil
cn.wolfcode.pss.domain;   // 装pss模块的domain类,模型对象.(Student)
cn.wolfcode.pss.dao;          //装pss模块的dao接口.
cn.wolfcode.pss.dao.impl; //装pss模块的dao接口的实现类.
cn.wolfcode.pss.test;          //暂时存储DAO的测试类,以后的测试类不应该放这里.</p> <p><strong>命名规范:</strong></p> <p>以下的 Xxx 表示一个模型对象，比如 Employee,Department,Student</p> <ul><li>DAO  接口 : 表示对某个模型的 CRUD 操作做规范，以 I 开头,interface
<ul><li>标准：IXxxDAO</li> <li>例： IEmployeeDAO/IStudentDAO</li></ul></li> <li>DAO 实现类: 表示对某个 DAO 接口的实现
<ul><li>标准：XxxDAOImpl</li> <li>例：EmployeeDAOImpl/StudentDAOImpl</li></ul></li> <li>DAO 测试类: 测试 DAO 组件中的所有方法
<ul><li>标准：XxxDAOTest: XxxDAO 组件的测试类,</li> <li>例：EmployeeDAOTest,StudentDAOTest</li></ul></li></ul> <p><strong>开发建议</strong>: 面向接口编程,声明 DAO 对象
传统的做法     : EmployeeDAOImpl dao = new EmployeeDAOImpl();
面向接口编程 : IEmployeeDAO    dao = new EmployeeDAOImpl();</p> <p>把实现类赋给接口类型,体现多态的特性:可以屏蔽不同子类之间实现的差异.</p> <h3 id="_5-4-根据-dao-规范搭建项目"><a href="#_5-4-根据-dao-规范搭建项目" class="header-anchor">#</a> 5.4 根据 DAO 规范搭建项目</h3> <h4 id="_5-4-1-步骤"><a href="#_5-4-1-步骤" class="header-anchor">#</a> 5.4.1 步骤</h4> <ol><li><p>创建项目</p></li> <li><p>导入数据库驱动包</p></li> <li><p>创建表和模型包以及模型对象 (domain/Student)</p></li> <li><p>创建 DAO 包和 DAO 接口,设计 DAO 接口方法 (dao/IStudentDAO)</p></li> <li><p>创建 DAO 实现包，实现 DAO 接口(dao.impl/StudentDAOImpl)</p></li> <li><p>创建测试目录，生成测试类和方法(test/StudentDAOTest)</p></li> <li><p>书写实现，实现一个方法测试一个方法并且测试通过</p></li></ol> <h4 id="_5-4-2-测试先行"><a href="#_5-4-2-测试先行" class="header-anchor">#</a> 5.4.2 测试先行</h4> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">StudentDAOTest</span> <span class="token punctuation">{</span>
    <span class="token keyword">private</span> <span class="token class-name">IStudentDAO</span> studetDAO <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">StudentDAOImpl</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token annotation punctuation">@Test</span>
    <span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">insert</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token class-name">Student</span> stu <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Student</span><span class="token punctuation">(</span><span class="token keyword">null</span><span class="token punctuation">,</span><span class="token string">&quot;小明&quot;</span><span class="token punctuation">,</span><span class="token string">&quot;ming@&quot;</span><span class="token punctuation">,</span><span class="token number">18</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        studetDAO<span class="token punctuation">.</span><span class="token function">insert</span><span class="token punctuation">(</span>stu<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token annotation punctuation">@Test</span>
    <span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">delete</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        studetDAO<span class="token punctuation">.</span><span class="token function">delete</span><span class="token punctuation">(</span><span class="token number">2L</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token annotation punctuation">@Test</span>
    <span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">update</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token class-name">Student</span> stu <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Student</span><span class="token punctuation">(</span><span class="token number">1L</span><span class="token punctuation">,</span><span class="token string">&quot;小明&quot;</span><span class="token punctuation">,</span><span class="token string">&quot;ming@&quot;</span><span class="token punctuation">,</span><span class="token number">19</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        studetDAO<span class="token punctuation">.</span><span class="token function">update</span><span class="token punctuation">(</span>stu<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token annotation punctuation">@Test</span>
    <span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token class-name">Student</span> stu <span class="token operator">=</span> studetDAO<span class="token punctuation">.</span><span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token number">1L</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>stu<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>

    <span class="token annotation punctuation">@Test</span>
    <span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">selectList</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
        <span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">Student</span><span class="token punctuation">&gt;</span></span> list <span class="token operator">=</span> studetDAO<span class="token punctuation">.</span><span class="token function">selectList</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token class-name">Student</span> stu<span class="token operator">:</span>list<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>stu<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br></div></div><h4 id="_5-4-3-dao-之保存操作"><a href="#_5-4-3-dao-之保存操作" class="header-anchor">#</a> 5.4.3 DAO 之保存操作</h4> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">insert</span><span class="token punctuation">(</span><span class="token class-name">Student</span> stu<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;INSERT INTO t_student(name,email,age) VALUES('&quot;</span> 
                                    <span class="token operator">+</span> stu<span class="token punctuation">.</span><span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token string">&quot;','&quot;</span>
                                    <span class="token operator">+</span> stu<span class="token punctuation">.</span><span class="token function">getEmail</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token string">&quot;',&quot;</span>
                                    <span class="token operator">+</span> stu<span class="token punctuation">.</span><span class="token function">getAge</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token string">&quot;)&quot;</span><span class="token punctuation">;</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">// 贾琏欲执事</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">Statement</span> st <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token comment">// 1 加载注册驱动</span>
        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 2 获取连接对象</span>
        conn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">&quot;jdbc:mysql:///jdbcdemo&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;admin&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 3 获取语句对象</span>
        st <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">createStatement</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 4 执行sql 语句</span>
        st<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            <span class="token keyword">if</span> <span class="token punctuation">(</span>st <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                st<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            <span class="token keyword">if</span> <span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br></div></div><p>在完成保存的过程中,执行 SQL 需要的参数是调用者传递进来的,所以需要将其拼接到 SQL 里面</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code> <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;INSERT INTO t_student(name,email,age) VALUES('&quot;</span> 
                                        <span class="token operator">+</span> stu<span class="token punctuation">.</span><span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token string">&quot;','&quot;</span>
                                        <span class="token operator">+</span> stu<span class="token punctuation">.</span><span class="token function">getEmail</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token string">&quot;',&quot;</span>
                                        <span class="token operator">+</span> stu<span class="token punctuation">.</span><span class="token function">getAge</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token string">&quot;)&quot;</span><span class="token punctuation">;</span>
        <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>可以看出,这里的拼接操作极其恶心, 容易错,还不容易发现,拼接多了语义很不清晰，所以这里需要优化（PreparedStatement）</p> <h2 id="六、预编译语句对象"><a href="#六、预编译语句对象" class="header-anchor">#</a> 六、预编译语句对象</h2> <h3 id="_6-1-预编译语句对象简介"><a href="#_6-1-预编译语句对象简介" class="header-anchor">#</a> 6.1 预编译语句对象简介</h3> <p>PreparedStatement 接口: 是 Statement 接口的子接口, 享有 Statement 中的方法.</p> <p>使用预编译语句对象,sql 语句中使用 ? 来作为值的占位符.</p> <h3 id="_6-2-api-详解-掌握"><a href="#_6-2-api-详解-掌握" class="header-anchor">#</a> 6.2 API 详解（掌握）</h3> <p>Connection API：</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token class-name">PreparedStatement</span> conn对象的<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span><span class="token class-name">String</span> sql<span class="token punctuation">)</span> 
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>PreparedStatement API：</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">// 常用方法:</span>
<span class="token keyword">void</span> <span class="token function">setXxx</span><span class="token punctuation">(</span><span class="token keyword">int</span> parameterIndex<span class="token punctuation">,</span><span class="token class-name">Xxx</span> value<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">//设置第几个占位符的真正参数值.</span>
    <span class="token comment">// Xxx 表示数据类型,比如 String,int,long,Date等.</span>
<span class="token keyword">void</span> <span class="token function">setObject</span><span class="token punctuation">(</span><span class="token keyword">int</span> parameterIndex<span class="token punctuation">,</span> <span class="token class-name">Object</span> x<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">//设置第几个占位符的真正参数值.</span>

<span class="token keyword">int</span> <span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">//执行DDL/DML语句. 注意:没有参数</span>
    <span class="token comment">// 若当前 SQL是 DDL语句,则返回 0.</span>
    <span class="token comment">// 若当前 SQL是 DML语句,则返回受影响的行数.</span>
<span class="token class-name">ResultSet</span> <span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">//执行DQL语句,返回结果集.</span>
<span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">//释放资源</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p>有了 PreparedStatement 就可以使用占位符 ? 来代替拼接，这样语义更加清晰，数据设置也很清晰，所以以后都使用 PreparedStatement 预编译语句对象。</p> <h3 id="_6-3-插入功能优化"><a href="#_6-3-插入功能优化" class="header-anchor">#</a> 6.3 插入功能优化</h3> <p>调整之后的插入操作：</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">insert</span><span class="token punctuation">(</span><span class="token class-name">Student</span> stu<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;INSERT INTO t_student(name,email,age) VALUES(?,?,?)&quot;</span><span class="token punctuation">;</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">// 贾琏欲执事</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> pst <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token comment">// 1 加载注册驱动</span>
        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 2 获取连接对象</span>
        conn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">&quot;jdbc:mysql:///jdbcdemo&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;admin&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 3 获取预编译语句对象</span>
        pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 给 sql 的 ？ 设置真实数据</span>
        pst<span class="token punctuation">.</span><span class="token function">setObject</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span>stu<span class="token punctuation">.</span><span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pst<span class="token punctuation">.</span><span class="token function">setObject</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span>stu<span class="token punctuation">.</span><span class="token function">getEmail</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pst<span class="token punctuation">.</span><span class="token function">setObject</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span>stu<span class="token punctuation">.</span><span class="token function">getAge</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token comment">// 4 执行sql 语句：注意（无参数）</span>
        pst<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            <span class="token keyword">if</span> <span class="token punctuation">(</span>pst <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                pst<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
        <span class="token keyword">try</span> <span class="token punctuation">{</span>
            <span class="token keyword">if</span> <span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
                conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
        <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
            e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br></div></div><h2 id="七、基于-dao-案例"><a href="#七、基于-dao-案例" class="header-anchor">#</a> 七、基于 DAO 案例</h2> <h3 id="_7-1-dao-之更新、删除"><a href="#_7-1-dao-之更新、删除" class="header-anchor">#</a> 7.1 DAO 之更新、删除</h3> <p>更新和删除都属于 DML 操作，它们和插入的不同仅仅就 SQL 语句不同而已，其他的都是相同的。</p> <p>DML 操作模板：</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">//String sql = &quot;INSERT INTO t_student(name,email,age) VALUES(?,?,?)&quot;;  // 增</span>
<span class="token comment">//String sql = &quot;DELETE FROM t_student WHERE id=?&quot;;					   // 删</span>
<span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;UPDATE t_student SET name=?,email=?,age=? WHERE id=?&quot;</span><span class="token punctuation">;</span>   <span class="token comment">// 改</span>
<span class="token comment">// 贾琏欲执事</span>
<span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token class-name">PreparedStatement</span> pst <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token keyword">try</span> <span class="token punctuation">{</span>
    <span class="token comment">// 1 加载注册驱动</span>
    <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">// 2 获取连接对象 [增删改一样]</span>
    conn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">&quot;jdbc:mysql:///jdbcdemo&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;admin&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">// 3 获取预编译语句对象 [增删改一样]</span>
    pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">// 给 SQL 的 ？ 设置数据</span>
    <span class="token comment">// ==== 添加的 SQL 的 ? 设置数据 ===</span>
    <span class="token comment">//pst.setString(1, stu.getName());</span>
    <span class="token comment">//pst.setString(2, stu.getEmail());</span>
    <span class="token comment">//pst.setInt(3, stu.getAge());</span>

    <span class="token comment">// ==== 删除的 SQL 的 ? 设置数据 ===</span>
    <span class="token comment">// pst.setLong(1,id);</span>

    <span class="token comment">// ==== 修改 SQL 的 ? 设置数据 ===</span>
    pst<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span>stu<span class="token punctuation">.</span><span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    pst<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span>stu<span class="token punctuation">.</span><span class="token function">getEmail</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    pst<span class="token punctuation">.</span><span class="token function">setInt</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span>stu<span class="token punctuation">.</span><span class="token function">getAge</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    pst<span class="token punctuation">.</span><span class="token function">setLong</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span>stu<span class="token punctuation">.</span><span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token comment">// 4 执行sql 语句</span>
    pst<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>pst <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            pst<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br></div></div><p>在此 DML 操作就完成了，目前就差 DQL 了。</p> <h3 id="_7-2-dao-之查询操作"><a href="#_7-2-dao-之查询操作" class="header-anchor">#</a> 7.2 DAO 之查询操作</h3> <p>DQL 操作和 DML 操作的不同之处是 DQL 需要获取返回的数据，而这些数据都被存在一个叫 ResultSet 的对象中</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token class-name">ResultSet</span> <span class="token class-name">Statement</span>对象的 <span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token class-name">String</span> sql<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">//执行DQL语句,返回结果集.</span>
<span class="token class-name">ResultSet</span> <span class="token class-name">PreparedStatement</span>对象的 <span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>   <span class="token comment">//执行DQL语句,返回结果集.</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h4 id="_7-2-1-查询结果对象"><a href="#_7-2-1-查询结果对象" class="header-anchor">#</a> 7.2.1_查询结果对象</h4> <p>ResultSet 接口:  通过执行 DQL 语句查询之后的结果对象.</p> <p>ResultSet 对象具有指向其当前数据行的光标。最初，光标被置于第一行之前。next 方法将光标移动到下一行，该方法在 ResultSet 对象没有下一行时返回 false，所以可以在 while 循环中使用它来迭代结果集</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">// 常用方法:select id,name,age</span>
<span class="token keyword">boolean</span> <span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">//判断当前光标是否能向下移动,能向下移动返回true,并同时将光标移动到下一行`.</span>

<span class="token class-name">Xxx</span> <span class="token function">getXxx</span><span class="token punctuation">(</span><span class="token keyword">int</span> columnIndex<span class="token punctuation">)</span><span class="token punctuation">;</span>  <span class="token comment">//取出当前光标所在行的第columnIndex列的数据(columnIndex 从1开始算).</span>
<span class="token class-name">Xxx</span> <span class="token function">getXxx</span><span class="token punctuation">(</span><span class="token class-name">String</span> columnName<span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">//取出当前光标所在行的列名为columnName列的数据,columnName可以是别名.</span>
<span class="token class-name">Xxx</span> <span class="token function">getObject</span><span class="token punctuation">(</span><span class="token class-name">String</span> columnName<span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">//取出当前光标所在行的列名为columnName列的数据,columnName可以是别名.</span>
<span class="token comment">// Xxx 表示数据类型,比如String,int,long,Date等. 推荐使用列名来取数据.</span>

<span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token comment">// 释放资源</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p>ResultSet 中存的数据则为查询出来的结果，这个结果就是一张表的结果。</p> <table><thead><tr><th>id</th> <th>name</th> <th>email</th> <th>age</th></tr></thead> <tbody><tr><td>1</td> <td>小明</td> <td>xiao@</td> <td>18</td></tr> <tr><td>2</td> <td>小东</td> <td>dong@</td> <td>18</td></tr></tbody></table> <p>而我们要做的就是从 ResultSet 中来取出这些数据。</p> <p><strong>操作步骤</strong></p> <ol><li><p>先取行:  默认光标在第一行，则为标题行（数据）</p> <p>使用 ResultSet 的 next 方法完成</p></li> <li><p>再取列: id  name  age</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">// 取出当前光标所在行的第columnIndex列的数据(columnIndex 从 1 开始算).</span>
<span class="token class-name">Xxx</span> <span class="token function">getXxx</span><span class="token punctuation">(</span><span class="token keyword">int</span> columnIndex<span class="token punctuation">)</span><span class="token punctuation">;</span>  <span class="token comment">// 索引可能会变化,所以不建议使用	</span>
<span class="token comment">// 取出当前光标所在行的列名为columnName列的数据,columnName可以是别名.(推荐)</span>
<span class="token class-name">Xxx</span> <span class="token function">getXxx</span><span class="token punctuation">(</span><span class="token class-name">String</span> columnName<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div></li></ol> <h4 id="_7-2-2-查询实现"><a href="#_7-2-2-查询实现" class="header-anchor">#</a> 7.2.2 查询实现</h4> <p>查询单个学生的信息</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">public</span> <span class="token class-name">Student</span> <span class="token function">selectOne</span><span class="token punctuation">(</span><span class="token class-name">Long</span> id<span class="token punctuation">)</span> 		
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;SELECT * FROM t_student WHERE id=?&quot;</span><span class="token punctuation">;</span>
	<span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
	<span class="token class-name">PreparedStatement</span> pst <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
	<span class="token class-name">ResultSet</span> rs <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
	<span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token comment">// 1 加载注册驱动</span>
        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 2 获取连接对象</span>
        conn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">&quot;jdbc:mysql:///jdbcdemo&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;admin&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 3 获取语句对象</span>
        pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 设置sql 的? 的值</span>
        pst<span class="token punctuation">.</span><span class="token function">setLong</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> id<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 4 执行语句操作</span>
        rs <span class="token operator">=</span> pst<span class="token punctuation">.</span><span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 从ResultSet 中来获取数据</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>rs<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token comment">// 获取列的数据</span>
            <span class="token keyword">long</span> resultId <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getLong</span><span class="token punctuation">(</span><span class="token string">&quot;id&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">String</span> name <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getString</span><span class="token punctuation">(</span><span class="token string">&quot;name&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">String</span> email <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getString</span><span class="token punctuation">(</span><span class="token string">&quot;email&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">int</span> age <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getInt</span><span class="token punctuation">(</span><span class="token string">&quot;age&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">Student</span> stu <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Student</span><span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> email<span class="token punctuation">,</span> age<span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">return</span> stu<span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
        <span class="token comment">// 释放资源</span>
    <span class="token punctuation">}</span>
	<span class="token keyword">return</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br></div></div><p>查询所有学生信息</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">public</span> <span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">Student</span><span class="token punctuation">&gt;</span></span> <span class="token function">selectList</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token comment">// 创建一个List 集合,用来存获取到的每一行数据封装成的 Student 对象</span>
    <span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">Student</span><span class="token punctuation">&gt;</span></span> list <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">ArrayList</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token punctuation">&gt;</span></span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;SELECT * FROM t_student&quot;</span><span class="token punctuation">;</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> pst <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">ResultSet</span> rs <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token comment">// 1 加载注册驱动</span>
        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 2 获取连接对象</span>
        conn <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">&quot;jdbc:mysql:///jdbcdemo&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;admin&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 3 获取语句对象</span>
        pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 4 执行语句操作</span>
        rs <span class="token operator">=</span> pst<span class="token punctuation">.</span><span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 从ResultSet 中来获取数据</span>
        <span class="token keyword">while</span> <span class="token punctuation">(</span>rs<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token comment">// 获取列的数据</span>
            <span class="token keyword">long</span> resultId <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getLong</span><span class="token punctuation">(</span><span class="token string">&quot;id&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">String</span> name <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getString</span><span class="token punctuation">(</span><span class="token string">&quot;name&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">String</span> email <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getString</span><span class="token punctuation">(</span><span class="token string">&quot;email&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">int</span> age <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getInt</span><span class="token punctuation">(</span><span class="token string">&quot;age&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">Student</span> stu <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Student</span><span class="token punctuation">(</span>resultId<span class="token punctuation">,</span> name<span class="token punctuation">,</span> email<span class="token punctuation">,</span> age<span class="token punctuation">)</span><span class="token punctuation">;</span>
            list<span class="token punctuation">.</span><span class="token function">add</span><span class="token punctuation">(</span>stu<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
        <span class="token comment">// 释放资源</span>
    <span class="token punctuation">}</span>
    <span class="token keyword">return</span> list<span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br></div></div><h2 id="八、重构设计"><a href="#八、重构设计" class="header-anchor">#</a> 八、重构设计</h2> <p>在 DAO 的实现过程中,发现释放资源的代码非常恶心,而且还需要反复编写,这种代码我们通常可以抽取到工具类中: JDBCUtil</p> <h3 id="_8-1-抽取-jdbcutil-工具类"><a href="#_8-1-抽取-jdbcutil-工具类" class="header-anchor">#</a> 8.1 抽取 JDBCUtil 工具类</h3> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">// JDBCUtil.java</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">close</span><span class="token punctuation">(</span><span class="token class-name">Connection</span> conn<span class="token punctuation">,</span> <span class="token class-name">Statement</span> pst<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token function">close</span><span class="token punctuation">(</span>conn<span class="token punctuation">,</span>pst<span class="token punctuation">,</span><span class="token keyword">null</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
<span class="token comment">// 释放资源</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">close</span><span class="token punctuation">(</span><span class="token class-name">Connection</span> conn<span class="token punctuation">,</span> <span class="token class-name">Statement</span> pst<span class="token punctuation">,</span> <span class="token class-name">ResultSet</span> rs<span class="token punctuation">)</span> <span class="token punctuation">{</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>rs <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            rs<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> 
    <span class="token comment">// 释放资源</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>pst <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            pst<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> 
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>conn <span class="token operator">!=</span> <span class="token keyword">null</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            conn<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br></div></div><p>在获取连接的过程,需要指定连接的信息:  驱动类的全限定名 / url / 用户名 /密码（连接数据库的四要素）, 但是每次都需要编写对应的字符串,麻烦，重复，也容易错。</p> <p>将获取连接对象的操作抽取到工具类中,统一编写连接数据库的信息</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">//JDBCUtil.java</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">Connection</span> <span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">Exception</span> <span class="token punctuation">{</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token comment">// 加载注册驱动</span>
        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 获取连接对象</span>
        <span class="token keyword">return</span> <span class="token class-name">DriverManager</span>
            		<span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token string">&quot;jdbc:mysql://localhost:3306/javaweb&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;root&quot;</span><span class="token punctuation">,</span> <span class="token string">&quot;admin&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
    <span class="token keyword">return</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><p>加载注册驱动在整个程序只需要执行一次,所以将加载的代码放到静态代码块中</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">//JDBCUtil.java</span>
<span class="token keyword">static</span> <span class="token punctuation">{</span>
    <span class="token comment">// 加载注册驱动</span>
    <span class="token comment">// 该操作,在整个程序中只需要执行一次</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">ClassNotFoundException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p>上面的代码中,我们是将连接数据库的信息编写在 Java 代码中的,这当中包括数据库的用账号和密码</p> <p>Java代码会编译成字节码文件,然后把字节码文件交给用户去使用,当用户需要修改数据库密码时,此时不方便。</p> <p><strong>问题</strong>: 硬编码</p> <p><strong>解决方案</strong>: 配置文件  properties(key-value)   ||   xml</p> <p>由于四要素是key=value 格式，所以选择使用 properties</p> <h3 id="_8-2-抽取db-properties"><a href="#_8-2-抽取db-properties" class="header-anchor">#</a> 8.2 抽取db.properties</h3> <div class="language-properties line-numbers-mode"><pre class="language-properties"><code><span class="token comment">## db.properties</span>
<span class="token key attr-name">driverClassName</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.jdbc.Driver</span>
<span class="token key attr-name">url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/javaweb</span>
<span class="token key attr-name">username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token key attr-name">password</span><span class="token punctuation">=</span><span class="token value attr-value">admin</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>将文件中的数据加载到内存中的 Properties 对象，然后再从 Properties 中获取数据，设置给 JDBC</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token comment">// JDBCUtil.java</span>
<span class="token keyword">private</span> <span class="token class-name">JDBCUtil</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span><span class="token punctuation">}</span>

<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token class-name">Properties</span> p <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Properties</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token punctuation">;</span>
<span class="token keyword">static</span> <span class="token punctuation">{</span>
    <span class="token comment">// 该操作,在整个程序中只需要执行一次</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        <span class="token comment">// properties配置文件只需要加载一次</span>
        p<span class="token punctuation">.</span><span class="token function">load</span><span class="token punctuation">(</span><span class="token class-name">Thread</span><span class="token punctuation">.</span><span class="token function">currentThread</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
               		<span class="token punctuation">.</span><span class="token function">getContextClassLoader</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
               		<span class="token punctuation">.</span><span class="token function">getResourceAsStream</span><span class="token punctuation">(</span><span class="token string">&quot;db.properties&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
		<span class="token comment">// 加载注册驱动</span>
        <span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span>p<span class="token punctuation">.</span><span class="token function">getProperty</span><span class="token punctuation">(</span><span class="token string">&quot;driverClassName&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>

<span class="token comment">// 获取连接对象</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">Connection</span> <span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">Exception</span> <span class="token punctuation">{</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
    	<span class="token comment">// 获取连接对象</span>
    	<span class="token keyword">return</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span>
        					p<span class="token punctuation">.</span><span class="token function">getProperty</span><span class="token punctuation">(</span><span class="token string">&quot;url&quot;</span><span class="token punctuation">)</span>
            				<span class="token punctuation">,</span> p<span class="token punctuation">.</span><span class="token function">getProperty</span><span class="token punctuation">(</span><span class="token string">&quot;username&quot;</span><span class="token punctuation">)</span>
            				<span class="token punctuation">,</span> p<span class="token punctuation">.</span><span class="token function">getProperty</span><span class="token punctuation">(</span><span class="token string">&quot;password&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
    <span class="token keyword">return</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br></div></div><h2 id="九、jdbc-事务操作"><a href="#九、jdbc-事务操作" class="header-anchor">#</a> 九、JDBC 事务操作</h2> <p>事务在 JDBC 中非常重要，没有事务是一件非常恐怖的事情，如下案例</p> <h3 id="_9-1-银行转账案例"><a href="#_9-1-银行转账案例" class="header-anchor">#</a> 9.1 银行转账案例</h3> <blockquote><p>需求: 银行转账, 从凯哥账户上给赵云转 1000 块钱.</p></blockquote> <h4 id="_9-1-1-准备-account-账户"><a href="#_9-1-1-准备-account-账户" class="header-anchor">#</a> 9.1.1 准备 account (账户)</h4> <table><thead><tr><th>id</th> <th>name(账号,唯一)</th> <th>balance(余额)</th></tr></thead> <tbody><tr><td>1</td> <td>凯哥</td> <td>20000</td></tr> <tr><td>2</td> <td>赵云</td> <td>0</td></tr></tbody></table> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> account<span class="token punctuation">(</span>
	id <span class="token keyword">BIGINT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    balance <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> account<span class="token punctuation">(</span>name<span class="token punctuation">,</span>balance<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'凯哥'</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'赵云'</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h4 id="_9-1-2-转账操作步骤"><a href="#_9-1-2-转账操作步骤" class="header-anchor">#</a> 9.1.2 转账操作步骤</h4> <ol><li>查询凯哥的账户余额是否大于等于1000.
<ul><li>SQL :  SELECT  *  FROM account WHERE balance &gt;= 1000 AND name='凯哥'
<ul><li>余额小于1000 :  温馨提示: 亲,你的余额不足.</li> <li>余额大于等于1000 : GOTO 2.</li></ul></li></ul></li> <li>从凯哥的账户余额中减少1000
<ul><li>SQL : UPDATE account SET balance = balance - 1000 WHERE name='凯哥'</li></ul></li> <li>在赵云的账户余额中增加1000
<ul><li>SQL : UPDATE account SET balance = balance +1000 WHERE name='赵云'</li></ul></li></ol> <p><strong>案例实现</strong>：</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">testTx</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">Exception</span> <span class="token punctuation">{</span>
    <span class="token comment">// 贾琏欲执事</span>
    <span class="token comment">// 1 查询凯哥的账户余额是否大于等于1000</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token class-name">JDBCUtil</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;SELECT * FROM  account WHERE balance&gt;=? AND name=?&quot;</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">// 给 ? 设置数据</span>
    pst<span class="token punctuation">.</span><span class="token function">setBigDecimal</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">new</span> <span class="token class-name">BigDecimal</span><span class="token punctuation">(</span><span class="token string">&quot;1000&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    pst<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">&quot;凯哥&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">ResultSet</span> rs <span class="token operator">=</span> pst<span class="token punctuation">.</span><span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">if</span><span class="token punctuation">(</span><span class="token operator">!</span>rs<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
        <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">&quot;余额不足&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">return</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
    
    <span class="token comment">// 2 从凯哥的账户余额中减少1000.</span>
    sql <span class="token operator">=</span> <span class="token string">&quot;UPDATE account SET balance = balance-? WHERE name=?&quot;</span><span class="token punctuation">;</span>
    pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">//设置? 的数据</span>
    pst<span class="token punctuation">.</span><span class="token function">setBigDecimal</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">new</span> <span class="token class-name">BigDecimal</span><span class="token punctuation">(</span><span class="token string">&quot;1000&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    pst<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">&quot;凯哥&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    pst<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token comment">// 模拟出异常或停电</span>
    <span class="token keyword">int</span> a <span class="token operator">=</span> <span class="token number">10</span><span class="token operator">/</span><span class="token number">0</span><span class="token punctuation">;</span>

    <span class="token comment">// 3 在赵云的账户余额中增加1000.</span>
    sql <span class="token operator">=</span> <span class="token string">&quot;UPDATE account SET balance = balance+? WHERE name=?&quot;</span><span class="token punctuation">;</span>
    pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">//设置? 的数据</span>
    pst<span class="token punctuation">.</span><span class="token function">setBigDecimal</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">new</span> <span class="token class-name">BigDecimal</span><span class="token punctuation">(</span><span class="token string">&quot;1000&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    pst<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">&quot;赵云&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    pst<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token comment">// 释放资源</span>
    <span class="token class-name">JDBCUtil</span><span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span>conn<span class="token punctuation">,</span>pst<span class="token punctuation">,</span>rs<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br></div></div><blockquote><p><strong>问题</strong>:  当程序执行到第 ②步和第 ③步中间时,突然出现一个异常或停电,此时会造成凯哥减了1000,而赵云未加1000的问题.</p> <p>造成这个问题的根本原因是,加减是两个单独的操作,加失败,减已经被执行,而转账业务中,需要保证加减两个操作要么都成功,要么都失败.</p></blockquote> <p>所以这里我们需要使用 InnoDB 存储引擎，用事务管理来解决这个问题。</p> <h3 id="_9-2-jdbc-的事务操作"><a href="#_9-2-jdbc-的事务操作" class="header-anchor">#</a> 9.2 JDBC 的事务操作</h3> <p>事务(Transaction): 简写为 tx</p> <blockquote><p>事务是指将一组操作括为一个单元,为确保数据库中数据的一致性,数据操作是成组的单元,当单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。</p></blockquote> <h4 id="_9-2-1-事务的-acid-属性-作用"><a href="#_9-2-1-事务的-acid-属性-作用" class="header-anchor">#</a> 9.2.1 事务的 ACID 属性(作用)</h4> <ol><li><p>原子性（Atomicity）: 原子在化学中,是最小单位,不可以再分割了.
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。</p></li> <li><p>一致性（Consistency): 保证数据的完整性.
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。(数据不被破坏)</p></li> <li><p>隔离性（Isolation）:
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。</p></li> <li><p>持久性（Durability）:
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响.</p></li></ol> <h4 id="_9-2-2-事务的操作步骤"><a href="#_9-2-2-事务的操作步骤" class="header-anchor">#</a> 9.2.2 事务的操作步骤</h4> <ol><li>先定义开始一个事务,然后对数据作修改操作,</li> <li>执行过程中,如果没有问题就提交(<strong>commit</strong>)事务,此时的修改将永久地保存下来</li> <li>如果执行过程中有问题(异常),回滚事务(<strong>rollback</strong>),数据库管理系统将放弃您所作的所有修改而回到开始事务时的状态。</li></ol> <h4 id="_9-2-3-事务的操作模板-掌握"><a href="#_9-2-3-事务的操作模板-掌握" class="header-anchor">#</a> 9.2.3 事务的操作模板(掌握)</h4> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">try</span><span class="token punctuation">{</span>
    <span class="token comment">//取消事务自动提交机制,设置为手动提交 </span>
	connection对象<span class="token punctuation">.</span><span class="token function">setAutoCommit</span><span class="token punctuation">(</span><span class="token boolean">false</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">//操作1</span>
    <span class="token comment">//操作2</span>
    <span class="token comment">//异常</span>
    <span class="token comment">//操作3</span>
    <span class="token comment">//....</span>
    <span class="token comment">//所有操作成功则 手动提交事务                          </span>
    connection对象<span class="token punctuation">.</span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span><span class="token keyword">catch</span><span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span><span class="token punctuation">{</span>
  	<span class="token comment">//处理异常</span>
    
   	<span class="token comment">//出现异常 回滚事务</span>
	connection对象<span class="token punctuation">.</span><span class="token function">rollback</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><h4 id="_9-2-4-转账案例优化"><a href="#_9-2-4-转账案例优化" class="header-anchor">#</a> 9.2.4 转账案例优化</h4> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">testTx</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">Exception</span> <span class="token punctuation">{</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> pst <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">ResultSet</span> rs <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token keyword">try</span><span class="token punctuation">{</span>
        <span class="token comment">// 贾琏欲执事</span>
        <span class="token comment">// 1 查询凯哥的账户余额是否大于等于1000</span>
        conn <span class="token operator">=</span> <span class="token class-name">JDBCUtil</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 事务设置为手动提交</span>
        conn<span class="token punctuation">.</span><span class="token function">setAutoCommit</span><span class="token punctuation">(</span><span class="token boolean">false</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token class-name">String</span> sql <span class="token operator">=</span> <span class="token string">&quot;SELECT * FROM  account WHERE balance&gt;=? AND name=?&quot;</span><span class="token punctuation">;</span>
        pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 给 ? 设置数据</span>
        pst<span class="token punctuation">.</span><span class="token function">setBigDecimal</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">new</span> <span class="token class-name">BigDecimal</span><span class="token punctuation">(</span><span class="token string">&quot;1000&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pst<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">&quot;凯哥&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        rs <span class="token operator">=</span> pst<span class="token punctuation">.</span><span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">if</span><span class="token punctuation">(</span><span class="token operator">!</span>rs<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
            <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token string">&quot;余额不足&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token keyword">return</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        <span class="token comment">// 2 从凯哥的账户余额中减少1000.</span>
        sql <span class="token operator">=</span> <span class="token string">&quot;UPDATE account SET balance = balance-? WHERE name=?&quot;</span><span class="token punctuation">;</span>
        pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">//设置 ? 的数据</span>
        pst<span class="token punctuation">.</span><span class="token function">setBigDecimal</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">new</span> <span class="token class-name">BigDecimal</span><span class="token punctuation">(</span><span class="token string">&quot;1000&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pst<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">&quot;凯哥&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pst<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token comment">// 模拟出异常或停电</span>
        <span class="token keyword">int</span> a <span class="token operator">=</span> <span class="token number">10</span><span class="token operator">/</span><span class="token number">0</span><span class="token punctuation">;</span>

        <span class="token comment">// 3 在赵云的账户余额中增加1000.</span>
        sql <span class="token operator">=</span> <span class="token string">&quot;UPDATE account SET balance = balance+? WHERE name=?&quot;</span><span class="token punctuation">;</span>
        pst <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">//设置 ? 的数据</span>
        pst<span class="token punctuation">.</span><span class="token function">setBigDecimal</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">new</span> <span class="token class-name">BigDecimal</span><span class="token punctuation">(</span><span class="token string">&quot;1000&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pst<span class="token punctuation">.</span><span class="token function">setString</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">&quot;赵云&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        pst<span class="token punctuation">.</span><span class="token function">executeUpdate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 没问题,提交事务</span>
        conn<span class="token punctuation">.</span><span class="token function">commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span><span class="token keyword">catch</span><span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span><span class="token punctuation">{</span>
        <span class="token comment">//出现异常 回滚事务</span>
        connection对象<span class="token punctuation">.</span><span class="token function">rollback</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span><span class="token keyword">finally</span><span class="token punctuation">{</span>
        <span class="token comment">// 释放资源</span>
        <span class="token class-name">JDBCUtil</span><span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span>conn<span class="token punctuation">,</span>pst<span class="token punctuation">,</span>rs<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br></div></div><h4 id="_9-2-5-事务相关注意事项"><a href="#_9-2-5-事务相关注意事项" class="header-anchor">#</a> 9.2.5 事务相关注意事项</h4> <ol><li><p>默认情况下,事务在执行完 DML 操作就自动提交.</p></li> <li><p>查询操作,其实是不需要事务的.但是,一般的,我们在开发中都把查询放入事务中.</p></li> <li><p>开发中,代码完全正确,没有异常,但是就是数据库中数据不变.</p> <p><code>意识</code> : 没有提交事务</p></li> <li><p>在 MySQL 中,只有 InnoDB 存储引擎支持事务,支持外键,MyISAM 不支持事务.</p></li> <li><p>以后事务我们不应该在 DAO 层处理,应该在 service 层控制(了解).</p></li> <li><p>事务在讲解 MyBatis,Spring,项目的时候都会再讲.</p></li></ol> <h2 id="十、连接池思想"><a href="#十、连接池思想" class="header-anchor">#</a> 十、连接池思想</h2> <h3 id="_10-1-连接池引入和介绍"><a href="#_10-1-连接池引入和介绍" class="header-anchor">#</a> 10.1 连接池引入和介绍</h3> <p>普通的 JDBC 数据库连接(Connection对象)使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 ·加载到内存中，再验证用户名和密码得花费 0.05s～1s 的时间,<code>时间成本比较大</code>。</p> <blockquote><p>需要数据库连接时，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。</p></blockquote> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200423081530626.png" alt="image-20200423081530626"><br></p> <p>数据库连接对象并没有得到很好的重复利用，若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。</p> <p>对于每一次数据库连接，使用完后都得断开，不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，可能导致内存泄漏，服务器崩溃.</p> <p><strong>解决</strong>:  先创建好 Connection 对象,存起来重复使用,如下图.存了 Connection 的容器则为连接池.</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200423081957007.png" alt="image-20200423081957007"><br></p> <h3 id="_10-2-使没使用连接池的区别"><a href="#_10-2-使没使用连接池的区别" class="header-anchor">#</a> 10.2 使没使用连接池的区别</h3> <table><thead><tr><th></th> <th>Connection 对象获取</th> <th>Connection 释放</th></tr></thead> <tbody><tr><td><strong>原始方式</strong></td> <td>DriverManager.getConnection(url,username,password)</td> <td>Connection对象.close()：是和数据库服务器断开</td></tr> <tr><td><strong>使用连接池</strong></td> <td>连接池对象.getConnection()</td> <td>Connection对象.close()：把 Connection对象返还给连接池中，并没有<br>和数据库服务器断开</td></tr></tbody></table> <p><strong>关键在于 连接池对象（DataSource） 的制作和使用</strong>.</p> <p><strong>如何制作连接池？</strong></p> <p>需要自定义容器来创建和管理连接对象，自己制作太麻烦，所以使用别人写好的。</p> <p>在 Java 中,连接池使用 javax.sql.DataSource 接口来表示连接池（Connection Pool），DataSource 仅仅只是一个接口,由各大服务器厂商来实现(Tomcat,JBoss).</p> <h3 id="_10-3-常见的-datasource-实现"><a href="#_10-3-常见的-datasource-实现" class="header-anchor">#</a> 10.3 常见的 DataSource 实现</h3> <ul><li><p>DBCP :  Spring 框架推荐的</p></li> <li><p>C3P0  :  Hibernate 框架推荐的</p></li> <li><p><strong>druid :  阿里巴巴的连接池(号称 Java 语言中性能最好的连接池).</strong></p></li></ul> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200423082651582.png" alt="image-20200423082651582"><br></p> <h3 id="_10-4-druid-连接池使用"><a href="#_10-4-druid-连接池使用" class="header-anchor">#</a> 10.4 druid 连接池使用</h3> <p>druid: 是阿里巴巴研发出来的号称 Java语言领域性能最高的连接池.
wiki地址:<a href="https://github.com/alibaba/druid/wiki" target="_blank" rel="noopener noreferrer">https://github.com/alibaba/druid/wiki<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p> <p><strong>特点</strong>：</p> <ul><li>方便检测性能/状态.</li> <li>支持: MySQL,Oracle,DB2,MS Server等.</li> <li>支持: 对配置文件的密码加密.</li></ul> <p><strong>使用</strong>：拷贝 jar: druid-1.0.15.jar 到项目中即可直接使用。</p> <p><strong>最基本的写法</strong>：</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">testDruidDataSource</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">Exception</span> <span class="token punctuation">{</span>
    <span class="token comment">// 创建一个连接池对象</span>
    <span class="token class-name">DruidDataSource</span> ds <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">DruidDataSource</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token comment">// 设置连接对象创建需要的四要素</span>
    ds<span class="token punctuation">.</span><span class="token function">setDriverClassName</span><span class="token punctuation">(</span><span class="token string">&quot;com.mysql.jdbc.Driver&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    ds<span class="token punctuation">.</span><span class="token function">setUrl</span><span class="token punctuation">(</span><span class="token string">&quot;jdbc:mysql://localhost:3306/jdbcdemo&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    ds<span class="token punctuation">.</span><span class="token function">setUsername</span><span class="token punctuation">(</span><span class="token string">&quot;root&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    ds<span class="token punctuation">.</span><span class="token function">setPassword</span><span class="token punctuation">(</span><span class="token string">&quot;admin&quot;</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
	<span class="token comment">// 其他参数的设置</span>
    ds<span class="token punctuation">.</span><span class="token function">setInitialSize</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  <span class="token comment">//初始化创建连接的个数</span>
    <span class="token comment">// 获取连接对象</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> ds<span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>conn<span class="token punctuation">.</span><span class="token function">getClass</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><p>参数设置API</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200423083855443.png" alt="image-20200423083855443"><br></p> <p>处理 Druid 连接池使用过程中的硬编码</p> <p>在创建连接池对象时,使用到的连接数据库的信息应该编写到 Properties 配置文件中,然后再读取到内存中来使用</p> <p>db.properties</p> <div class="language-properties line-numbers-mode"><pre class="language-properties"><code><span class="token comment">#这里的 key 一定要和 DruidDataSource 中对应的属性名一致</span>
<span class="token key attr-name">driverClassName</span><span class="token punctuation">=</span><span class="token value attr-value">com.mysql.jdbc.Driver</span>
<span class="token key attr-name">url</span><span class="token punctuation">=</span><span class="token value attr-value">jdbc:mysql://localhost:3306/javaweb</span>
<span class="token key attr-name">username</span><span class="token punctuation">=</span><span class="token value attr-value">root</span>
<span class="token key attr-name">password</span><span class="token punctuation">=</span><span class="token value attr-value">admin</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>使用内置 API 来直接使用属性文件中的数据，从而快速创建连接池对象。</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200424083008658.png" alt="image-20200424083008658"><br></p> <p><strong>Druid 连接池使用总结</strong>：</p> <ol><li>拷贝 druid-1.0.15.jar 到项目</li> <li>配置 db.properties 书写连接需要的属性</li> <li>连接池对象获取：DruidDataSourceFactory.createDataSource(p);</li> <li>连接对象获取：dataSource对象.getConnection();</li> <li>归还连接对象: Connection对象.close();</li></ol> <h3 id="_10-5-连接池其他功能"><a href="#_10-5-连接池其他功能" class="header-anchor">#</a> 10.5 连接池其他功能</h3> <p>对连接对象做管理限制的功能配置，不同连接池，属性名不同，内容一样。</p> <table><thead><tr><th>功能</th> <th>数值</th> <th>描述</th></tr></thead> <tbody><tr><td>初始化连接数</td> <td>5</td> <td>在连接池中事先准备好 5 个 Connection对象</td></tr> <tr><td>最多连接数</td> <td>10</td> <td>在连接池中最多有 10 个 Connection 对象,其他客户端进入等待状态</td></tr> <tr><td>最少连接数</td> <td>3</td> <td>在连接池中最少存在 3 个 Connection 对象</td></tr> <tr><td>最长等待时间</td> <td>5</td> <td>使用5秒来申请获取 Connection 对象,如果时间到还没有申请到,则提示,自动放弃</td></tr> <tr><td>最大超时时间</td> <td>10</td> <td>如果在10秒之内没有任何动作,则认为是自动放弃 Connection 对象</td></tr></tbody></table> <h2 id="十一、sql-注入详解"><a href="#十一、sql-注入详解" class="header-anchor">#</a> 十一、SQL 注入详解</h2> <p><strong>Statement 和 PreparedStatement 的区别,PreparedStatement 存在的优势 (了解)</strong></p> <ol><li><p>更好的可读性,可维护性.</p></li> <li><p>可以提供更好的性能(预编译). MySQL 不支持  PreparedStatement 性能优化.</p></li></ol> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200424083953923.png" alt="image-20200424083953923"><br></p> <ol start="3"><li><p>更安全,可以防止 SQL 注入的问题</p> <p>如果使用 Statement 语句对象,我们是把参数直接拼接到 SQL 中然后执行,此时,如果参数会改变SQL</p></li></ol> <p>的语法结构,执行的结果就会存在问题了,如,在登录查询的 SQL 中,如果用户名参数值为: <strong>' or 1=1 or</strong></p> <p><strong>'</strong>, 此时,填写的密码无论是多少,登录都会成功, 这就是 SQL 注入的问题.</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200424084100803.png" alt="image-20200424084100803"><br></p> <p>这个问题可以使用预编译语句对象完美解决</p> <ol><li><p>预先发送带有占位符的 SQL 到数据库中进行编译, 语句结构固定下来</p></li> <li><p>设置参数给对应的占位符,然后再执行 SQL</p></li></ol> <p>这里无论是什么参数,都不会再改变 SQL 的语法结构,达到防止 SQL 注入问题的目的.</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/image-20200424084237327.png" alt="image-20200424084237327"><br></p> <h2 id="十二、jdbc操作重构"><a href="#十二、jdbc操作重构" class="header-anchor">#</a> 十二、JDBC操作重构</h2> <p>查询操作模板代码抽取</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code>	<span class="token comment">/**
	 * 处理查询操作
	 * &lt;T&gt;:是声明泛型类型
	 * List&lt;T&gt;, Class&lt;T&gt; 使用声明好的 T
	 * @param sql 要执行的 SQL语句
	 * @param type 将每行数据封装的对象类型
	 * @param params 执行的 SQL需要的参数
	 * @return 返回查询到的结果,统一放到 List 集合中
	 */</span>
<span class="token keyword">public</span> <span class="token keyword">static</span>  <span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">T</span><span class="token punctuation">&gt;</span></span> <span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">T</span><span class="token punctuation">&gt;</span></span>  <span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token class-name">String</span> sql<span class="token punctuation">,</span><span class="token class-name">Class</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">T</span><span class="token punctuation">&gt;</span></span> type<span class="token punctuation">,</span> <span class="token class-name">Object</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>params<span class="token punctuation">)</span><span class="token punctuation">{</span>
    <span class="token class-name">Connection</span> conn <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">PreparedStatement</span> ps <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">ResultSet</span> rs <span class="token operator">=</span> <span class="token keyword">null</span><span class="token punctuation">;</span>
    <span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">T</span><span class="token punctuation">&gt;</span></span> list <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">ArrayList</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token punctuation">&gt;</span></span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">try</span> <span class="token punctuation">{</span>
        conn <span class="token operator">=</span> <span class="token class-name">DruidUtil</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        ps <span class="token operator">=</span> conn<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>sql<span class="token punctuation">)</span><span class="token punctuation">;</span>

        <span class="token comment">//为占位符设值</span>
        <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token keyword">int</span> i <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span> i <span class="token operator">&lt;</span> params<span class="token punctuation">.</span>length<span class="token punctuation">;</span> i<span class="token operator">++</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            ps<span class="token punctuation">.</span><span class="token function">setObject</span><span class="token punctuation">(</span>i <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span> params<span class="token punctuation">[</span>i<span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>

        rs <span class="token operator">=</span> ps<span class="token punctuation">.</span><span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token comment">// 直到next方法返回false时结束</span>
        <span class="token keyword">while</span> <span class="token punctuation">(</span>rs<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
            <span class="token comment">// 获取这一行中的指定列的数据</span>
            <span class="token comment">//使用反射创建对象</span>
            <span class="token class-name">T</span> t <span class="token operator">=</span> type<span class="token punctuation">.</span><span class="token function">newInstance</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token comment">//将数据从结果集中获取到,并设置给对象t</span>
            <span class="token comment">//通常情况下,属性名和列名一样,所以我们可以根据属性名去获取对应列的值</span>
            <span class="token class-name">BeanInfo</span> beanInfo <span class="token operator">=</span> <span class="token class-name">Introspector</span><span class="token punctuation">.</span><span class="token function">getBeanInfo</span><span class="token punctuation">(</span>t<span class="token punctuation">.</span><span class="token function">getClass</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token class-name">Object</span><span class="token punctuation">.</span><span class="token keyword">class</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">PropertyDescriptor</span><span class="token punctuation">[</span><span class="token punctuation">]</span> pds <span class="token operator">=</span> beanInfo<span class="token punctuation">.</span><span class="token function">getPropertyDescriptors</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token comment">//操作每个属性</span>
            <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token class-name">PropertyDescriptor</span> pd <span class="token operator">:</span> pds<span class="token punctuation">)</span> <span class="token punctuation">{</span>
                <span class="token comment">//获取到属性名</span>
                <span class="token class-name">String</span> name <span class="token operator">=</span> pd<span class="token punctuation">.</span><span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token comment">//根据这个属性名从结果集中获取到数据</span>
                <span class="token class-name">Object</span> value <span class="token operator">=</span> rs<span class="token punctuation">.</span><span class="token function">getObject</span><span class="token punctuation">(</span>name<span class="token punctuation">)</span><span class="token punctuation">;</span>
                <span class="token comment">//获取到属性对应的set方法</span>
                <span class="token class-name">Method</span> writeMethod <span class="token operator">=</span> pd<span class="token punctuation">.</span><span class="token function">getWriteMethod</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
                writeMethod<span class="token punctuation">.</span><span class="token function">invoke</span><span class="token punctuation">(</span>t<span class="token punctuation">,</span> value<span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token punctuation">}</span>
            list<span class="token punctuation">.</span><span class="token function">add</span><span class="token punctuation">(</span>t<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">}</span>
    <span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">Exception</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
        e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span> <span class="token keyword">finally</span> <span class="token punctuation">{</span>
        <span class="token class-name">JdbcUtil</span><span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span>conn<span class="token punctuation">,</span> ps<span class="token punctuation">,</span> rs<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">}</span>
    <span class="token keyword">return</span> list<span class="token punctuation">;</span>
<span class="token punctuation">}</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br></div></div><h2 id="十三、小结与作业"><a href="#十三、小结与作业" class="header-anchor">#</a> 十三、小结与作业</h2> <h3 id="_13-1-小结"><a href="#_13-1-小结" class="header-anchor">#</a> 13.1 小结</h3> <ul><li>理解什么是持久化</li> <li>理解 JDBC 的作用</li> <li>掌握 JDBC 快速入门和开发口诀</li> <li>掌握DriverManager、Connection、Statement  三个类的作用以及常用API</li> <li>掌握 DAO 思想与 DAO 规范</li> <li>掌握基于 JDBC 的 DAO 案例</li> <li>重点掌握 JDBCUtil 的抽取精华</li> <li>重点掌握 db.properties 文件的抽取(解决硬编码)</li> <li>重点掌握程序如何使用事务</li> <li>重点掌握 druid 连接池的使用</li></ul> <h3 id="_13-2-作业"><a href="#_13-2-作业" class="header-anchor">#</a> 13.2 作业</h3> <h4 id="_13-2-1-题目1"><a href="#_13-2-1-题目1" class="header-anchor">#</a> 13.2.1 题目1</h4> <p>新建模型重新练习</p> <p>1 从零开始书写一遍基于 DAO 规范的 crud 操作.(表自行定义:employee,person,user,目的: 掌握 crud 操作以及 DAO 规范)</p> <p>2 抽取 JDBCUtil 工具类</p> <p>3 抽取 db.properties</p> <p>4 书写带有事务  TX  的程序(只要在程序中加入事物即可,不知道的情况: 使用课堂上的转账案例去巩固事物操作)</p> <p>5 把程序改为使用 druid 连接池 (修改 JDBCUtil   也可以 书写一个新的工具类 DruidUtil)</p> <p>6 以上操作不熟悉,重复写</p> <p>7 复习 sql 语句,简单的 crud 操作,基本的过滤条件,排序,分页语法 (MySQL练习.txt)</p> <h4 id="_13-2-2-题目2"><a href="#_13-2-2-题目2" class="header-anchor">#</a> 13.2.2 题目2</h4> <p>模拟登录操作:控制台版本的登录操作(测试类+DAO)</p> <p>1 当用户输入的账号错误时,提示用户账号错误</p> <p>2 当用户输入的密码错误时,提示用户密码错误</p> <h4 id="_13-2-3-题目3"><a href="#_13-2-3-题目3" class="header-anchor">#</a> 13.2.3 题目3</h4> <p>1、分析京东网站，购物车表格有哪些字段，创建出表格</p> <p>2、完成加入购物车功能所需要的 SQL 语句</p> <p>3、 使用 JDBC 和 DAO 规范，来完成控制台版本的加入购物车功能</p> <p>4、使用 JDBC 和 DAO 规范，来完成控制台版本的购物车中某个商品增加一个数据量的功能</p></div></div>  <div class="page-edit"><!----> <div class="tags"><a href="/tags/?tag=JDBC" title="标签">#JDBC</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/pages/826257/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">MySQL基础</div></a> <a href="/pages/9980e4/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">MyBatis框架初级</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/pages/826257/" class="prev">MySQL基础</a></span> <span class="next"><a href="/pages/9980e4/">MyBatis框架初级</a>→
      </span></p></div></div></div> <div class="article-list"><div class="article-title"><a href="/archives/" class="iconfont icon-bi">最近更新</a></div> <div class="article-wrapper"><dl><dd>01</dd> <dt><a href="/pages/0c07b2/"><div>
            IT杂货铺
            <!----></div></a> <span class="date">03-23</span></dt></dl><dl><dd>02</dd> <dt><a href="/pages/705b35/"><div>
            人事百问
            <!----></div></a> <span class="date">03-22</span></dt></dl><dl><dd>03</dd> <dt><a href="/pages/78eb56/"><div>
            MyBatis-Plus
            <!----></div></a> <span class="date">03-12</span></dt></dl> <dl><dd></dd> <dt><a href="/archives/" class="more">更多文章&gt;</a></dt></dl></div></div></main></div> <div class="footer"><div class="icons"><a href="mailto:langfeiyes@163.com" title="发邮件" target="_blank" class="iconfont icon-youjian"></a><a href="https://github.com/langfeiyes" title="GitHub" target="_blank" class="iconfont icon-github"></a><a href="https://music.163.com/#/playlist?id=755597173" title="听音乐" target="_blank" class="iconfont icon-erji"></a></div> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2024-2024
    <span><a href="https://beian.miit.gov.cn/" target="_blank">粤ICP备18007927号-1</a></span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"><div></div></div></div>
    <script src="/assets/js/app.a2a00aa5.js" defer></script><script src="/assets/js/2.256f807a.js" defer></script><script src="/assets/js/34.45e8e898.js" defer></script>
  </body>
</html>
